--删除大对象函数
create or replace function qk_lo_unlink(loid1 oid) returns boolean as $qk$
declare
	loid2 oid;
begin
	select pl.loid into loid2 from pg_largeobject pl where pl.loid=loid1 limit 1 offset 0;
	if loid2 is null then
		raise notice '大对象%不存在',loid1;
		return false;
	end if;
	return lo_unlink(loid1);
end;
$qk$ language plpgsql;

DROP TABLE IF EXISTS "menu_tree_path";
DROP TABLE IF EXISTS "menu";
DROP TABLE IF EXISTS "page";
DROP TABLE IF EXISTS "dept";
DROP TABLE IF EXISTS "dept_tree_path";

CREATE TABLE "dept" (
  "id" 				serial			PRIMARY KEY,
  "code"			VARCHAR(30)	NOT NULL,
  "lbl"				VARCHAR(30)	NOT NULL DEFAULT '',
  "type"			VARCHAR(10)	NOT NULL DEFAULT '',
  "emp_id"			int				NOT NULL DEFAULT 0,
  "desc"			VARCHAR(250)	NOT NULL DEFAULT '',
  "is_root"			boolean		NOT NULL DEFAULT true,
  "is_leaf"			boolean		NOT NULL DEFAULT true
);
COMMENT ON TABLE "dept" 					is '部门';
COMMENT ON COLUMN "dept"."code" 			is '编码';
COMMENT ON COLUMN "dept"."lbl" 			is '名称';
COMMENT ON COLUMN "dept"."type" 			is '类型';
COMMENT ON COLUMN "dept"."emp_id" 			is '负责人';
COMMENT ON COLUMN "dept"."desc" 			is '描述';
COMMENT ON COLUMN "dept"."is_root" 		is '根节点';
COMMENT ON COLUMN "dept"."is_leaf" 		is '子节点';
insert into "dept" ("id","code","lbl","type","emp_id","desc","is_root","is_leaf") values 
(1,'tpm','总经理','',0,'',true,true),
(2,'vcm','副总经理','',0,'',true,true),
(3,'fad','财务部','',0,'',true,true),
(4,'hrd','人事部','',0,'',true,true),
(5,'mkt','市场部','',0,'',true,true),
(6,'atd','行政部','',0,'',true,true),
(7,'gld','后勤部','',0,'',true,true);
select setval('dept_id_seq',(select max(id) from dept));
CREATE TABLE "dept_tree_path" (
  "id" serial PRIMARY KEY,
  "ancestor" int NOT NULL DEFAULT 0,
  "descendant" int NOT NULL DEFAULT 0,
  "path_length" int NOT NULL DEFAULT 0
);
insert into "dept_tree_path" ("ancestor","descendant","path_length") values 
(1,1,0),(1,2,1),(1,3,2),(1,4,2),(1,5,2),(1,6,2),(1,7,2),(2,2,0),(2,3,1),(2,4,1),(2,5,1),(2,6,1),(2,7,1),(3,3,0),(4,4,0),(5,5,0),(6,6,0),(7,7,0);
create unique index dept_code_unique on "dept"("code");

DROP TABLE IF EXISTS "role";
CREATE TABLE "role" (
  "id" 				serial			PRIMARY KEY,
  "code"			varchar(30)	NOT NULL,
  "lbl" 			varchar(25)	NOT NULL DEFAULT '',
  "desc"			VARCHAR(250)	NOT NULL DEFAULT '',
  "is_root"			boolean		NOT NULL DEFAULT true,
  "is_leaf"			boolean		NOT NULL DEFAULT true
);
create unique index role_code_unique on "role"("code");
COMMENT ON TABLE  "role" 					is '角色';
COMMENT ON COLUMN "role"."code" 			is '编码';
COMMENT ON COLUMN "role"."lbl" 			is '名称';
COMMENT ON COLUMN "role"."desc" 			is '描述';
COMMENT ON COLUMN "role"."is_root" 		is '根节点';
COMMENT ON COLUMN "role"."is_leaf"			is '叶子';
insert into "role" ("id","code","lbl","desc","is_root","is_leaf") values
(1,'root','超级管理员','',true,false)
;
select setval('role_id_seq',(select max(id) from role));
DROP TABLE IF EXISTS "role_tree_path";
CREATE TABLE "role_tree_path" (
  "id" serial PRIMARY KEY,
  "ancestor" int NOT NULL DEFAULT 0,
  "descendant" int NOT NULL DEFAULT 0,
  "path_length" int NOT NULL DEFAULT 0
);
insert into "role_tree_path"("id","ancestor","descendant","path_length") values
(1,1,1,0)
;
select setval('role_tree_path_id_seq',(select max(id) from role_tree_path));

DROP TABLE IF EXISTS "role_emp";
CREATE TABLE "role_emp" (
  "id" 				serial			PRIMARY KEY,
  "role_id" 		int 			NOT NULL DEFAULT 0,
  "emp_id" 			int 			NOT NULL DEFAULT 0
);
COMMENT ON TABLE  "role_emp" 					is '角色员工';
COMMENT ON COLUMN "role_emp"."role_id" 		is '角色';
COMMENT ON COLUMN "role_emp"."emp_id" 			is '员工';
insert into "role_emp" ("role_id","emp_id") values (1,1);
select setval('role_emp_id_seq',(select max(id) from role_emp));

DROP TABLE IF EXISTS "dept_emp";
CREATE TABLE "dept_emp" (
  "id" 				serial			PRIMARY KEY,
  "dept_id" 		int 			NOT NULL DEFAULT 0,
  "emp_id" 			int 			NOT NULL DEFAULT 0
);
COMMENT ON TABLE "dept_emp" 					is '部门员工';
COMMENT ON COLUMN "dept_emp"."dept_id" 		is '部门';
COMMENT ON COLUMN "dept_emp"."emp_id" 			is '员工';
insert into "dept_emp" ("dept_id","emp_id") values (1,1);
select setval('dept_emp_id_seq',(select max(id) from dept_emp));

DROP TABLE IF EXISTS "emp";
CREATE TABLE "emp" (
  "id" 				serial			PRIMARY KEY,
  "code" 			varchar(25) 	NOT NULL,
  "name" 			varchar(25) 	NOT NULL DEFAULT '',
  "password" 		varchar(50) 	NOT NULL,
  "sex" 			varchar(5) 	NOT NULL DEFAULT '',
  "addr" 			varchar(100) 	NOT NULL DEFAULT '',
  "wtel" 			varchar(30) 	NOT NULL DEFAULT '',
  "htel"			varchar(30) 	NOT NULL DEFAULT '',
  "mtel" 			varchar(30) 	NOT NULL DEFAULT '',
  "birthdate"		date		 	DEFAULT NULL,
  "origin" 			varchar(30) 	NOT NULL DEFAULT '',
  "com_addr" 		varchar(100) 	NOT NULL DEFAULT '',
  "zip" 			varchar(10) 	NOT NULL DEFAULT '',
  "card" 			varchar(50) 	NOT NULL DEFAULT '',
  "nation" 			varchar(20) 	NOT NULL DEFAULT '',
  "religiou" 		varchar(30) 	NOT NULL DEFAULT '',
  "edu_lvl" 		varchar(20) 	NOT NULL DEFAULT '',
  "school" 			varchar(50) 	NOT NULL DEFAULT '',
  "politic" 		varchar(20) 	NOT NULL DEFAULT '',
  "occup" 			varchar(20) 	NOT NULL DEFAULT '',
  "job" 			varchar(30) 	NOT NULL DEFAULT '',
  "position" 		varchar(30) 	NOT NULL DEFAULT '',
  "interest" 		varchar(30) 	NOT NULL DEFAULT '',
  "skill" 			varchar(30) 	NOT NULL DEFAULT '',
  "marital" 		varchar(20) 	NOT NULL DEFAULT '',
  "health" 			varchar(100) 	NOT NULL DEFAULT '',
  "fax" 			varchar(30) 	NOT NULL DEFAULT '',
  "email" 			varchar(30) 	NOT NULL DEFAULT '',
  "qq" 				varchar(30) 	NOT NULL DEFAULT '',
  "msn" 			varchar(30) 	NOT NULL DEFAULT '',
  "person" 			varchar(200) 	NOT NULL DEFAULT '',
  "introduction"	varchar(200) 	NOT NULL DEFAULT '',
  "picture" 		varchar(200) 	NOT NULL DEFAULT '',
  "enable" 			boolean 		NOT NULL DEFAULT TRUE
);
COMMENT ON TABLE "emp" 					is '员工信息';
COMMENT ON COLUMN "emp"."code" 			is '编码';
COMMENT ON COLUMN "emp"."name" 			is '姓名';
COMMENT ON COLUMN "emp"."password" 		is '密码';
COMMENT ON COLUMN "emp"."sex"		 		is '性别';
COMMENT ON COLUMN "emp"."addr"		 		is '家庭地址';
COMMENT ON COLUMN "emp"."wtel"		 		is '办公电话';
COMMENT ON COLUMN "emp"."htel"		 		is '家庭电话';
COMMENT ON COLUMN "emp"."mtel"		 		is '移动电话';
COMMENT ON COLUMN "emp"."birthdate"		is '出生日期';
COMMENT ON COLUMN "emp"."origin"			is '籍贯';
COMMENT ON COLUMN "emp"."com_addr"			is '通信地址';
COMMENT ON COLUMN "emp"."zip"				is '邮编';
COMMENT ON COLUMN "emp"."card"				is '身份证号';
COMMENT ON COLUMN "emp"."nation"			is '民族';
COMMENT ON COLUMN "emp"."religiou"			is '宗教信仰';
COMMENT ON COLUMN "emp"."edu_lvl"			is '教育程度';
COMMENT ON COLUMN "emp"."school"			is '毕业学校';
COMMENT ON COLUMN "emp"."politic"			is '政治面貌';
COMMENT ON COLUMN "emp"."occup"			is '职业类型';
COMMENT ON COLUMN "emp"."job"				is '职务';
COMMENT ON COLUMN "emp"."position"			is '职位';
COMMENT ON COLUMN "emp"."interest"			is '爱好';
COMMENT ON COLUMN "emp"."skill"			is '特长';
COMMENT ON COLUMN "emp"."marital"			is '婚姻状况';
COMMENT ON COLUMN "emp"."health"			is '健康状况';
COMMENT ON COLUMN "emp"."fax"				is '传真';
COMMENT ON COLUMN "emp"."email"			is '邮箱';
COMMENT ON COLUMN "emp"."qq"				is 'QQ';
COMMENT ON COLUMN "emp"."msn"				is 'MSN';
COMMENT ON COLUMN "emp"."person"			is '性格描述';
COMMENT ON COLUMN "emp"."introduction"		is '个人简述';
COMMENT ON COLUMN "emp"."picture"			is '照片';
COMMENT ON COLUMN "emp"."enable"			is '启用';
INSERT INTO "emp" ("id","name","code","password") VALUES (1,'admin','admin','admin123');
select setval('emp_id_seq',(select max(id) from emp));
create unique index emp_code_unique on "emp"("code");
create or replace function emp_largeobject() returns trigger as $qk$
declare
	eny2 record;
	tmpBoolean boolean;
begin
	if tg_op = 'INSERT' then
		if new.picture is null or new.picture = '' then
			return new;
		end if;
		new.picture = lo_import(new.picture);
		return new;
	end if;
	if tg_op = 'UPDATE' then
		if new.picture is null or new.picture = '' then
			return new;
		end if;
		tmpBoolean = qk_lo_unlink(old.picture::oid);
		new.picture = lo_import(new.picture);
		return new;
	end if;
	if tg_op = 'DELETE' then
		if old.picture is null or old.picture = '' then
			return old;
		end if;
		tmpBoolean = qk_lo_unlink(old.picture::oid);
		return old;
	end if;
end;
$qk$ language plpgsql;
create trigger emp_largeobject_uid before insert or update or delete on emp for each row execute procedure emp_largeobject();

CREATE TABLE "page" (
  "id" serial PRIMARY KEY,
  "code" varchar(50) UNIQUE NOT NULL DEFAULT '',
  "lbl" varchar(50) NOT NULL DEFAULT '',
  "url" varchar(500) NOT NULL DEFAULT ''
);
COMMENT ON TABLE "page" IS '页面';
INSERT INTO "page" VALUES
 (1,'PageList','页面列表','/web/sys/PageList.html')
,(2,'MenuList','菜单列表','/web/sys/MenuList.html')
,(3,'EmpT2','员工标签页','/web/sys/EmpT2.html')
,(4,'DeptList','部门列表','/web/sys/DeptList.html')
,(5,'RoleList','角色列表','/web/sys/RoleList.html')
;
select setval('page_id_seq',(select max(id) from page));

CREATE TABLE "menu" (
  "id" serial PRIMARY KEY,
  "lbl" varchar(50) NOT NULL DEFAULT '',
  "open_op" varchar(50) NOT NULL DEFAULT '',
  "page_id" int NOT NULL DEFAULT 0,
  "is_root" boolean NOT NULL DEFAULT FALSE,
  "is_leaf" boolean NOT NULL DEFAULT FALSE,
  "enable" boolean NOT NULL DEFAULT TRUE,
  "sort_num" int
);
COMMENT ON TABLE "menu" IS '菜单';
INSERT INTO "menu" ("id","lbl","open_op","page_id","is_root","is_leaf","enable","sort_num") VALUES 
 (1,'系统管理','', DEFAULT, '1', '0', '1',1)
,(2,'页面','tab', 1, '0', '1', '1',2)
,(3,'菜单','tab', 2, '0', '1', '1',3)
,(4,'基础数据','', DEFAULT, '1', '0', '1',4)
,(5,'员工','tab', 3, '0', '1', '1',5)
,(6,'部门','tab', 4, '0', '1', '1',6)
,(7,'角色','tab', 5, '0', '1', '1',7)
;
select setval('menu_id_seq',(select max(id) from menu));
CREATE TABLE "menu_tree_path" (
  "id" serial PRIMARY KEY,
  "ancestor" int NOT NULL DEFAULT 0,
  "descendant" int NOT NULL DEFAULT 0,
  "path_length" int NOT NULL DEFAULT 0
);
INSERT INTO "menu_tree_path" ("id","ancestor","descendant","path_length") VALUES
 (1,1,1,0)
,(2,1,2,1)
,(3,2,2,0)
,(4,1,3,1)
,(5,3,3,0)
,(6,4,4,0)
,(7,4,5,1)
,(8,5,5,0)
,(9,4,6,1)
,(10,6,6,0)
,(11,4,7,1)
,(12,7,7,0)
;
select setval('menu_tree_path_id_seq',(select max(id) from menu_tree_path));

--sort_num
create or replace function menu_sort_num() returns trigger as $qk$
declare
	eny2 record;
	max_sort_num int;
begin
	if (tg_op = 'UPDATE') or (tg_op = 'INSERT') then
		if new.sort_num = 0 then
			select max(sort_num) into max_sort_num from menu;
			update menu set sort_num=max_sort_num+1 where new.id=id;
			return new;
		end if;
		select * into eny2 from menu where sort_num = new.sort_num and new.id!=id limit 1 offset 0;
		if eny2 is not null then
			update menu set sort_num=sort_num+1 where id=eny2.id;
		end if;
	end if;
	return new;
end;
$qk$ language plpgsql;
create trigger menu_sort_num_ui after insert or update on menu for each row execute procedure menu_sort_num();
--sort_num
